// jjq
// 查找待本部门回复的工单数据
def muReplyWaitJson_jjq(Object parameter){
    def sql = '''
         select t1.PkId as myPkVal,WfId,OriLevel,SDeptID as fmDept,SBDeptID as sbDept,isUrge,
            t2.WorkFormNO as myTwfNO,t2.WsTopic as myTwfTopic,t2.fmContent as FmContent,
            (select SortName from bSort where CodePk=str(left(t3.DCntACode,4),4)) dCntAName1,
            (select SortName from bSort where CodePk=str(left(t3.DCntACode,6),6)) dCntAName2,
            (select SortName from bSort where CodePk=str(left(t3.DCntACode,8),8)) dCntAName3,
            SeqName,SBDeptName,SortName,DealName,convert(varchar(10),BackLimit,20) as bkLimit,SDeptName,convert(varchar(10),GetTime,20) as acDate,
            BackState as bkState,BackRemark as acRemark,t1.PkId as opDeal,BackDesc as backDesc,subBackState cbackDesc 
            from tWfSendBack t1 left join tWorkFormBase t2 on t1.WfId=t2.PkId left join tTelGetDeal t3 on t1.GetDealPk = t3.PkId 
            where t1.SBDeptID=#{param1} and t1.SendState in(21,23) and t1.BackState in (0,12,22) and t1.IsDel=0 and t2.IsDel=0 
    '''
    if (parameter?.workFormNO)
        sql += " and t2.WorkFormNO = #{workFormNO} "
    if (parameter?.backDesc)
        sql += " and t1.subBackState=#{backDesc} "
    if(parameter?.beginTime)
        sql += " and convert(varchar(10),t1.BackLimit,120) >= #{beginTime} "
    if(parameter?.endTime)
        sql += " and convert(varchar(10),t1.BackLimit,120) <= #{endTime} "
    if(parameter?.sdeptName)
        sql += " and t1.SDeptName =#{sdeptName} "
    if(parameter?.formType)
        sql += " and t1.isUrge = #{formType} "
    return sql
}

// px
// 查找待本部门回复的工单数据
def muReplyWaitJson_px(Object parameter){
    def sql = '''
         select t1.PkId as myPkVal,WfId,OriLevel,SDeptID as fmDept,SBDeptID as sbDept,isUrge,
            t2.WorkFormNO as myTwfNO,t2.WsTopic as myTwfTopic,t2.fmContent as FmContent,
            (select SortName from bSort where CodePk=str(left(t3.DCntACode,4),4)) dCntAName1,
            (select SortName from bSort where CodePk=str(left(t3.DCntACode,6),6)) dCntAName2,
            (select SortName from bSort where CodePk=str(left(t3.DCntACode,8),8)) dCntAName3,
            SeqName,SBDeptName,SortName,DealName,convert(varchar(10),BackLimit,20) as bkLimit,SDeptName,convert(varchar(10),GetTime,20) as acDate,
            BackState as bkState,BackRemark as acRemark,t1.PkId as opDeal,BackDesc as backDesc,dbo.tWfNOBackDesc(WfId,#{param1}) cbackDesc 
            from tWfSendBack t1 left join tWorkFormBase t2 on t1.WfId=t2.PkId left join tTelGetDeal t3 on t1.GetDealPk = t3.PkId 
            where t1.SBDeptID=#{param1} and t1.SendState in(21,23) and t1.BackState in (0,12,22) and t1.IsDel=0 and t2.IsDel=0 
    '''
    if (parameter?.workFormNO)
        sql += " and t2.WorkFormNO = #{workFormNO} "
    if (parameter?.backDesc)
        sql += " and #{backDesc} = dbo.tWfNOBackDesc(WfId,#{param1}) "
    if(parameter?.beginTime)
        sql += " and convert(varchar(10),t1.BackLimit,120) >= #{beginTime} "
    if(parameter?.endTime)
        sql += " and convert(varchar(10),t1.BackLimit,120) <= #{endTime} "
    if(parameter?.sdeptName)
        sql += " and t1.SDeptName =#{sdeptName} "
    if(parameter?.formType)
        sql += " and t1.isUrge = #{formType} "
    return sql
}

// 查找待本部门回复的工单数据 高新区
def muReplyWaitJson_gxq(Object parameter){
    def sql = '''
         select t1.PkId as myPkVal,WfId,OriLevel,SDeptID as fmDept,SBDeptID as sbDept,isUrge,
            t2.WorkFormNO as myTwfNO,t2.WsTopic as myTwfTopic,t2.fmContent as FmContent,
            (select SortName from bSort where CodePk=str(left(t3.DCntACode,4),4)) dCntAName1,
            (select SortName from bSort where CodePk=str(left(t3.DCntACode,6),6)) dCntAName2,
            (select SortName from bSort where CodePk=str(left(t3.DCntACode,8),8)) dCntAName3,
            SeqName,SBDeptName,SortName,DealName,convert(varchar(10),BackLimit,20) as bkLimit,SDeptName,convert(varchar(10),GetTime,20) as acDate,
            BackState as bkState,BackRemark as acRemark,t1.PkId as opDeal,BackDesc as backDesc,subBackState cbackDesc,SendState 
            from tWfSendBack t1 left join tWorkFormBase t2 on t1.WfId=t2.PkId left join tTelGetDeal t3 on t1.GetDealPk = t3.PkId 
            where t1.SBDeptID=#{param1} and t1.SendState in(21,23) and t1.BackState in (0,12,22) and t1.IsDel=0 and t2.IsDel=0 AND DATEDIFF(day, GETDATE(), t1.BackLimit) > -30 
    '''
    if (parameter?.workFormNO)
        sql += " and t2.WorkFormNO = #{workFormNO} "
    if (parameter?.backDesc)
        sql += " and t1.subBackState=#{backDesc} "
    if(parameter?.beginTime)
        sql += " and convert(varchar(10),t1.BackLimit,120) >= #{beginTime} "
    if(parameter?.endTime)
        sql += " and convert(varchar(10),t1.BackLimit,120) <= #{endTime} "
    if(parameter?.sdeptName)
        sql += " and t1.SDeptName =#{sdeptName} "
    if(parameter?.formType)
        sql += " and t1.isUrge = #{formType} "
    return sql
}

// 查询临期工单数量
def dueWorkOrderCount(Object parameter){
    def sql = '''
        select count(1) 
        from tWfSendBack t1 left join tWorkFormBase t2 on t1.WfId=t2.PkId left join tTelGetDeal t3 on t1.GetDealPk = t3.PkId 
        where t1.SBDeptID=#{parameter} and t1.IsDel=0 and t2.IsDel=0 and DATEDIFF(hour,GetDate(),BackLimit) > 0 and DATEDIFF(hour,GetDate(),BackLimit) < 48
        and t1.SendState in(21,23) and t1.BackState in (0,12,22) 
    '''
    return sql
}


 // 查找审核本级回复的工单数据
def muReplyChkJsonData(Object parameter){
    def sql = '''
            select t1.PkId as myPkVal,WfId,OriLevel,SDeptID as fmDept,SBDeptID as sbDept,WorkFormNO as myTwfNO,
            WsTopic as myTwfTopic,SeqName,SortName,DealName,SDeptName,convert(varchar(10),BackLimit,20) as bkLimit,
            SBDeptName,convert(varchar(10),BackTime,20) as bkDate,BackState as chgBack,t1.PkId as opDeal 
            from tWfSendBack t1 
            left join tWorkFormBase t2 on t1.WfId = t2.PkId and t1.IsDel = 0 and t2.IsDel = 0 
            left join tm_tWfSendBack_temp t3 on t1.PkId = t3.sbId 
            where t1.SBDeptID=#{param1} and t1.BackState=10 and t3.sbId is null and DATEDIFF(day,GetDate(),t1.BackLimit) > -30 
            '''
    if (parameter.workFormNO)
        sql += " and t2.WorkFormNO = #{workFormNO} "
    if (parameter.sdeptName)
        sql += " and t2.SDeptName =#{sdeptName} "
    if (parameter.opUserName)
        sql += " and t2.SUserName =#{opUserName} "
    return sql;
}

//查找审核本级回复的工单总数
def countMuReplyChkJson(Object parameter){
    def sql = '''
            select count(1) from tWfSendBack t1 left join tWorkFormBase t2 on t1.WfId = t2.PkId and t1.IsDel = 0 and t2.IsDel = 0 
            left join tm_tWfSendBack_temp t3 on t1.PkId = t3.sbId
            where t1.SBDeptID=cast(#{parameter} as varchar) and t1.BackState=10 and t3.sbId is null and DATEDIFF(day,GetDate(),t1.BackLimit) > -30 
        '''
    return sql;
}

// 加载(区县)退回的工单T列表数据
def backPeerDeptAuditTJson_gxq(Object parameter){
    def sql = '''
            select PkId as myPkVal,WfId,GetDealPK as telId,
            (select WorkFormNO from tWorkFormBase where PkId=WfId) as myTwfNO,
            (select WsTopic from tWorkFormBase where PkId=WfId) as myTwfTopic,
            SeqName,SBDeptName,SortName,DealName,convert(varchar(10),BackLimit,20) bkLimit,
            SDeptName,convert(varchar(10),SendTime,20) sdDate,convert(varchar(10),GetTime,20) as bkDate,backState,
            BackRemark,PkId as opDeal from tWfSendBack where SBDeptID=convert(varchar,#{param1}) and SendState=50 and IsDel=0 AND DATEDIFF(day, GETDATE(), BackLimit) > -30 
        '''
    if (parameter.workFormNO)
        sql += " and (select WorkFormNO from tWorkFormBase where PkId=WfId) = #{workFormNO} "
    if (parameter.sdeptName)
        sql += " and SDeptName =#{sdeptName} "
    return sql;
}

